Exploratory Data Analysis

The Challenge

Finance has the responsibility to forecast, plan and allocate resources in order to successfully deliver the best treatments to our patients.

• The first challenge consist of deliver sales forecast for 2018 per cluster, per brand and per month.

• The second challenge is to provide the optimal allocation of their resources for the year 2018.

Executive summary

• An extensive exploratory data analysis has been carried out. It starts looking the aggregated levels of the data and, little by little, it focuses on more detailed aspects until it reaches the product level.

• After analyzing the data, it can be concluded that a few products have most of the weight of the portfolio. Therefore, reducing the forecast error on this products will provide better savings to Novartis.

• Different time series models have been evaluated with a holdout split. For every product, the best model has been selected to generate the final forecast. The overall MAPE is 15%.

• To solve the allocation problem, multiple scenarios have been generated in order to find the best combination of investments for 2018.

Data preparation

All the data was provided in a single Excel file. This file contains for each brand of products two geographical levels (cluster and country), sales and investments in a monthly granularity.

Looking at the file, it seems a direct export from their CRM, time series data in wide format, with the totals of each quarter. This dataset needs some cleaning before starting to work with it.

Cluster	Brand Group	Country	Function	Jan 2012	Feb 2012	Mar 2012	...8
Cluster 3	Brand Group 12	Country 19	Sales 1	1861.3	1995.9	1998.5	5855.8
Cluster 3	Brand Group 12	Country 19	Sales 2	1316.2	1450.2	1440.8	4207.3
Cluster 3	Brand Group 12	Country 19	Investment 1	-216.2	-238.2	-217.5	-671.8
Cluster 3	Brand Group 12	Country 19	Investment 2	-107.4	-14.2	-62.1	-183.8
Cluster 3	Brand Group 12	Country 19	Investment 4	0.0	0.0	0.0	0.0

The dimensions of the file are:

[1] 4228  123

Summary of the provided data:

• Cluster (9): Highest level of geographical break down

• Country (53): Lowest level of geographical break down

• Brand Group (53): A pharmaceutical product

• Function (8): Financial information related to:

  • Sales 1: Gross sales = volume sold * price
  • Sales 2: Net Sales = Sales 1 - Revenue deduction/ etc.
  • Investment 1-6: different investment types

Sales data is available from 2012 through 2017, while investments are available through 2018.

Cleaning up

As mentioned before, we are going to clean up the dataset with the following steps:

• Remove totals of each quarter
• Rename Brand Group to brand
• Convert all headers to lower strings

df_raw <- df_raw %>% 
  rename(brand = `Brand Group`) %>% 
  select(-contains("...")) %>% 
  set_names(tolower)

cluster	brand	country	function	jan 2012	feb 2012	mar 2012	apr 2012
Cluster 3	Brand Group 12	Country 19	Sales 1	1861.3	1995.9	1998.5	2019.2
Cluster 3	Brand Group 12	Country 19	Sales 2	1316.2	1450.2	1440.8	1564.1
Cluster 3	Brand Group 12	Country 19	Investment 1	-216.2	-238.2	-217.5	-214.9
Cluster 3	Brand Group 12	Country 19	Investment 2	-107.4	-14.2	-62.1	-91.9

If we wanted to perform forecasts at the lowest possible level (cluster, country, brand) how many SKU we should have to forecast?

[1] "There are a total of 1184 SKU's to forecast"

Re-code brands

The forecast is only required for the following brands

data_wide <- df_raw %>% 
  mutate(
    brand = case_when(
      brand %in% "Brand Group 17" ~ "Brand Group 17",
      brand %in% "Brand Group 24" ~ "Brand Group 24",
      brand %in% "Brand Group 30" ~ "Brand Group 30",
      brand %in% "Brand Group 31" ~ "Brand Group 31",
      brand %in% "Brand Group 36" ~ "Brand Group 36",
      brand %in% "Brand Group 41" ~ "Brand Group 41",
      brand %in% paste("Brand Group", c(51, 73, 90)) ~ "Brand Group 51, 73, 90",
      brand %in% paste("Brand Group", c(96, 97)) ~ "Brand Group 96, 97",
      TRUE ~ "Others"
    )
)

cluster	brand	country	function	jan 2012	feb 2012	mar 2012	apr 2012
Cluster 3	Others	Country 19	Sales 1	1861.3	1995.9	1998.5	2019.2
Cluster 3	Others	Country 19	Sales 2	1316.2	1450.2	1440.8	1564.1
Cluster 3	Others	Country 19	Investment 1	-216.2	-238.2	-217.5	-214.9
Cluster 3	Others	Country 19	Investment 2	-107.4	-14.2	-62.1	-91.9
Cluster 3	Others	Country 19	Investment 4	0.0	0.0	0.0	0.0
Cluster 3	Others	Country 19	Investment 6	-57.0	-55.7	-64.7	-57.0

After this aggregations, we have:

[1] "There are 75 SKU's to forecast"

Reshape the data

Convert the data.frame to long format using pivot_longer and pivot_wider. Now, all the date columns have disappeared and all the dates appear in the date column. The data frame also contains sales and investments columns.

data_long <- data_wide %>% 
  mutate(
    `function` = `function` %>% str_to_lower() %>% str_replace(" ", "_") 
  ) %>% 
  pivot_longer(
    cols = -c(cluster, brand, country, `function`), 
    names_to = "date", 
    values_to = "value",
    names_transform = list(date = ~ parse_date_time(.x, orders = "my") ), 
  ) %>% 
  pivot_wider(
    id_cols = c(cluster, brand, country, date),
    names_from = `function`, 
    names_sort = TRUE,
    values_from = value,
    values_fn = sum
  ) %>% 
  select(cluster:date, starts_with("sales"), starts_with("investment"))

cluster	brand	country	date	sales_1	sales_2	investment_1	investment_2	investment_3	investment_4	investment_5	investment_6
Cluster 3	Others	Country 19	2012-01-01	46952.6	38956.6	-216.2	-759.8	-569.5	0.0	-890.5	-126.8
Cluster 3	Others	Country 19	2012-02-01	44180.0	34631.3	-239.5	-340.4	-199.3	0.0	-913.8	-135.9
Cluster 3	Others	Country 19	2012-03-01	50465.6	39905.6	-216.2	-691.2	-1634.8	2.6	-1152.0	-137.2

Sales Visual Overview

We will start our visual exploration by investigating a number of time series plots on different aggregation levels. Then, we will continue exploring the relationship of the sales with their own past sales and the different investment.

All aggregate sales

• The overall sales present an uptrend, specially after 2016.
• There is a dip in sales in august.

Let’s break down the sales into geographies and brands.

Geography

There are two geographical levels: Cluster and Country.

Cluster

Highest level of geographical break down

[1] "Cluster 1"  "Cluster 2"  "Cluster 3"  "Cluster 4"  "Cluster 5"  "Cluster 7" 
[7] "Cluster 8"  "Cluster 9"  "Cluster 10"

Registered S3 method overwritten by 'GGally':
  method from   
  +.gg   ggplot2

Form the image below we can see that how Cluster 10, Cluster 3, and Cluster 2 are the most important ones regarding sales, and Cluster 7 is the less important. It seems that there are two groups of clusters, one with the top three and other with the rest of the clusters.

Let’s see how the sales look like in the past. This time we want to explore visually if some clusters are more trending than others, are stationary or do present seasonal patterns.

• Cluster 4 seems quite seasonal
• Cluster 3 is the most trending one. Cluster 8 and Cluster 10 also have some trend.
• Cluster 1, 5 and 7 are stable, do not increase/decrease over this period.

It is interesting to see how some clusters concentrate many countries while others are simply formed by one. However, despite these differences, the number of brands in all the countries is 8 or 9 except in Cluster 7 with 5 brands.

Another way of looking at the data is by it’s contribution to the portfolio. How much weight each country has? Almost 60% of the portfolio belongs to Clusters 10, Cluster 3 and Cluster 2.

• Cluster 1 contains most countries, followed by Cluster 10 and Cluster 5 with 36, 6 and 4 countries respectively.

• Clusters 2, 3, 4, 7 and 8 only have one country.

Cluster	Number of countries	Number of brands	Weight
Cluster 1	36	9	0.07
Cluster 2	1	8	0.19
Cluster 3	1	8	0.20
Cluster 4	1	9	0.09
Cluster 5	4	9	0.05
Cluster 7	1	5	0.02
Cluster 8	1	9	0.11
Cluster 9	2	9	0.08
Cluster 10	6	9	0.20

Another way of looking at this data is with a tree map plot. As bigger the square it is more contribution to the portfolio they have, and as darker the shade it is, more countries in each cluster there are.

Country

Lowest level of geographical break down

[1] "Number of countries: 53"

 [1] "Country 10" "Country 11" "Country 12" "Country 13" "Country 14" "Country 15"
 [7] "Country 16" "Country 17" "Country 18" "Country 19"

• Most sales come from few countries
• There are many countries with very few sales
• Countries like Country 25, 20 or 40 might have outliers. Before treat them as such, we should investigate if these values are due to increase/decrease in investments, marketing, etc.
• Country 40 has high variability, as well as negative sales values.

All negative sales belong to cluster 1. There are 14 countries with negative values but without a doubt the most important one is Country 40 with 52 months with negative sales.

In case that we would like to see the 14 countries with negative sales

# Code to count cluster-countries with negative sales
by_country %>% 
  filter(value < 0) %>% 
  count(cluster, country, sort = TRUE)

We confirm that the two biggest countries are Country 19 and Country 17. The same shade of blue is maintained throughout the matrix, indicating that most countries have the same number of brands.

Brand Group

A Pharmaceutical product

[1] "Others"                 "Brand Group 17"         "Brand Group 24"        
[4] "Brand Group 31"         "Brand Group 36"         "Brand Group 41"        
[7] "Brand Group 51, 73, 90" "Brand Group 96, 97"     "Brand Group 30"        

Brand Others is the most significant at the sales level and the one with greatest dispersion. This can be for all the brands that it groups (see re-coding brands). On the other hand, Brand 41, Brand 30 and Brand 36 are the ones with less dispersion.

Below we can see that:

• Seven out of nine brands are in an uptrend. Brand Group 41 and Others are the both in a downtrend with similar trend structure.
• Brand Group 17 and Brand Group 24 present an exponential trend. Both brands starts between 2015 and 2016.
• Brand Group 30, Brand Group 31 and Brand Group 51, 73, 90 are in an uptrend up to some point in time and then it becomes to remain almost flat for the rest of the time.
• Brand Others has dips in august, as does total sales.

All levels

The forecast is required at the Cluster - Brand level. Recall that initially 1184 SKU’s have been provided and after the aggregation of the series we end up with 75 SKU’s.

In this section we will be able to answer question such as: Do the same brand follow the same pattern despite they do not belong to the same cluster?

all_levels <- data %>%
  group_by(cluster, brand, date) %>%
  summarise_at(vars(sales_1:investment_6), sum, na.rm = TRUE) %>%
  ungroup()

# Save this dataset for later use
write_rds(all_levels, file = file.path(path_data, "data_level_sku.rds"))

# Keep only with train data for better visualizations
all_levels <- all_levels %>%
  filter(date <= last_train)

With the following code we plot all the brands colored by cluster. However, we will only plot those cases that have remarkable features.

all_levels %>% 
  plot_time_series(x = date, y = sales_2, colour = cluster) +
  facet_wrap(~ brand, ncol = 3, scales = "fixed")

Some brands are more recent than others. Brand Group 17 and Brand Group 24 are examples of recent brands. where they began to be marketed from the beginning and end of 2015 respectively.

In the case of Brand Group 41 we can see a lot of variability within clusters, having in some clusters values below 10k and in others above 25k. On the other hand, it is interesting to see how the same brand behaves in a similar way in very cluster. This indicate us that this could be a good way to train our models.

We can also analyze the contribution of each product in the portfolio. More than 75% of the products have a weight less than or equal to 2%, while the other 25% have a weight bigger than 2%. We can also see how around 12% (9 products) of the products have a weights greater than 4%. This suggests that we should put more focus into some products than others.

Finally, we can visualize a tree map of the weights of each product. Notice how most important products belong to Others and Brand Group 41. Bigger squares and darker color means higher contribution to the portfolio.

Lagged sales

Many times the impact of the immediate past sales is not representative of what will come next. Imagine that we want to predict the temperature that we will have in august in an specific country. It would make sense to look at the past Augusts and see what is the average temperature rather than looking at the temperature of April or June.

On the x-axis is represented the lagged value of the sales and in the y-axis the real value. As closer the points are to the diagonal line better the correlation it is. Last year sales (12 months) seems a good predictor of current sales.

plot_data %>% 
  ggplot(aes(x = lag_value, y = sales_2)) +
  geom_point(shape = 21, colour = "black", fill = "orange", size = 3) +
  geom_smooth(method = lm, se = FALSE, colour = "black", lty = 2) +
  scale_x_continuous(
    labels = scales::label_number(suffix = "K", scale = 1e-3)
  ) +
  scale_y_continuous(
    labels = scales::label_number(suffix = "K", scale = 1e-3)
  ) +
  facet_wrap(~ lag_id, ncol = 3, scales = "fixed")

Investments

Investments

Overall investments

What is the most important investment type? In absolute terms, the most important investment are investment_1 and investment_2.

Sales vs Investments

To see the relationships between sales and investments, we can plot each time series against the others. These plots can be arranged in a scatterplot matrix, using the GGally package.

In this plot, mostly negative relationships with sales_2 are revealed, with the strongest relationships being with investment_1 and investment_5 and the weakest with investment_4.

• Investments 1, 5 and 6 are highly correlated with sales_2
• Investments 1, 5and 6 are highly correlated between them. Be cautious when modeling!
• Investment 3 has an outlier on the left tail of the distribution.
• Investment 4 does not have any effect over sales_2

Three out of six investments have a negative trend, therefore, it indicates that more and more has been invested. Recall that investments are negative and lower investments means more investing.

On the other hand, there is another group of investments that are stationary, that is, they are moving around their mean. In addition, investment_2 has annual seasonality, with spikes in august and September and valleys in December.

In January of 2016 there is an outlier in the investment_3.

Lagged Investments

Sometimes, the impact of a predictor that is included in a regression model will not be simple and immediate. For example, an advertising campaign may impact sales for some time beyond the end of the campaign, and sales in one month will depend on the advertising expenditure in each of the past few months.